package com.xbongbong.paas.toolbox.util;

/**
 * Description: 拼音相关
 *
 * @author 李杰
 * @version v1.0
 * @date 2019/1/28 10:13
 * @since v1.0
 */
public class PinyinUtil {

    /**
     * 获取某字段拼音首字母,该方法只是拼出获取该字段获取拼音首字母的sql方法，并不直接获取结果，
     * 需要结合sql查询获取需要的结果
     * 例如：
     * SELECT id,
     * IF((LEFT(CONVERT(NAME USING gbk),1) REGEXP '[a-zA-Z]') = 1, UPPER(LEFT(CONVERT(NAME USING gbk),1)),
     * ELT(INTERVAL(CONV(HEX(LEFT(CONVERT(NAME USING gbk ), 1)),16,10),0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,0xC8F6,0xCBFA,0xCDDA ,0xCEF4,0xD1B9,0xD4D1),'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z'))  AS firstPinyin,
     * department,avatar,user_id,NAME,role_ids,is_boss
     * FROM tb_user
     * WHERE 1
     * AND corpid = '1' AND del = 0
     * ORDER BY CONVERT(NAME USING gbk) ASC
     *
     * @param field 需要获取该字段的首字母拼音
     * @return
     */
    public static String formatFirstPinyinStr(String field) {

        StringBuilder firstPinyinStr = new StringBuilder();
        firstPinyinStr.append("IF((LEFT(CONVERT(");
        firstPinyinStr.append(field);
        firstPinyinStr.append(" USING gbk),1) REGEXP '[a-zA-Z]') = 1, UPPER(LEFT(CONVERT(");
        firstPinyinStr.append(field);
        firstPinyinStr.append(" USING gbk),1)),");
        firstPinyinStr.append(" if(LEFT(CONVERT(name USING gbk ), 1)='岑','C',ELT(INTERVAL(CONV(HEX(LEFT(CONVERT(");
        firstPinyinStr.append(field);
        firstPinyinStr.append(" USING gbk ), 1)),16,10),0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,0xC8F6,0xCBFA,0xCDDA ,0xCEF4,0xD1B9,0xD4D1),'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z')))");

        return firstPinyinStr.toString();
    }
}
